package com.eascs.web.o2o.scheduler.history;

import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.Calendar;
import java.util.Date;
import java.util.List;

import javax.inject.Inject;

import org.springframework.stereotype.Service;

import com.alibaba.dubbo.common.utils.StringUtils;
import com.eascs.app.dao.DefaultIdBuilder;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.app.log.Logger;
import com.eascs.app.log.LoggerFactory;
import com.eascs.common.util.StopWatch;
import com.eascs.common.util.StringUtil;
import com.eascs.web.o2o.scheduler.dao.BaseDao;
import com.eascs.web.o2o.scheduler.dao.ServiceDao;
import com.eascs.web.o2o.scheduler.entity.BasicInformationAfterLoan;
import com.eascs.web.o2o.scheduler.entity.BasicInformationOverdue;
import com.eascs.web.o2o.scheduler.entity.LoanBaseInfoEntity;
import com.eascs.web.o2o.scheduler.entity.O2oOverdueReport;
import com.eascs.web.o2o.scheduler.entity.PaymentsPlan;
import com.eascs.web.o2o.scheduler.entity.ReportPingtaiDelivery;
import com.eascs.web.o2o.scheduler.util.Constant;

@Service
public class AdpterAfterLoanDataService {

	protected final Logger logger = LoggerFactory.getLogger(AdpterAfterLoanDataService.class);
	@Inject
	BaseDao baseDao;
	@Inject
	ServiceDao serviceDao;

	@Inject
	PaymentPlanService  paymentPlanService;
	
	/**
	 * 同步贷后基础信息
	 * 
	 * 报表数据同步逻辑：
	 * 首先同步台账放款数据，然后通过逾期数据，然后根据还款计划和逾期状态同步放款余额
	 * 
	 * 
	   报表字段逻辑：
	 * 累计投放总额：所有已放款的金额
	 * 正常余额：到期还未还的金额
	 * 本金逾期：当状态为本金逾期状态，取拖欠总额为本金逾期金额
	 * 利息逾期：当状态为利息状态，取拖欠总额为利息逾期金额
	 * 代偿：当状态为代偿状态，取处置总额为代偿金额
	 * 罚息：取罚息金额为罚息金额
	 * basic_information_overdue存放导入的逾期数据，由于存在一个单多条数据，所以以这个表做中间表
	 * basic_information_after_loan：贷后数据最终表
	 * 
	 * 
	 * 
	 */
	
	
	/**
	 * 
	 * @Title: saveBasiInformationAfterLoan
	 * @Description: 全量同步贷后数据，包含新老系统数据
	 * @author user
	 * @throws Exception
	 * @return void
	 * @throws
	 * @date 2017年1月4日 下午7:47:15
	 */
	public void saveBasiInformationAfterLoan() throws Exception{
		StopWatch stop = new StopWatch();
		logger.info("开始贷后数据同步", "");
		
		//1.从基本信息表中初始化放款编号，放款金额
		logger.info("开始同步放款编号", "");
			initData();
		logger.info("结束同步放款编号");
		//2.同步逾期数据
		logger.info("开始同步逾期数据");
			synchronousOverDueData();
		logger.info("结束同步逾期数据");
		//3.同步还款计划
		logger.info("开始同步还款计划");
			//paymentPlanService.savePaymentPlan();
		logger.info("结束同步还款计划");
		//4.同步还款状态
		logger.info("开始同步还款状态");
			synchRepayStatus();
		logger.info("结束同步还款状态");
		//5.同步放款余额
		logger.info("开始同步放款余额");
			synchronousLoanData();
		logger.info("结束同步放款余额");
		
		logger.info("结束同步贷后，总耗时[{}]秒===================", stop.elapsedTime()/1000);
	}
	
	/**
	 * 
	 * @Title: saveBasiInformationAfterLoan
	 * @Description: 增量同步贷后数据，只包含新系统
	 * @author user
	 * @throws Exception
	 * @return void
	 * @throws
	 * @date 2017年1月4日 下午7:47:15
	 */
	public void saveNewBasiInformationAfterLoan() throws Exception{
		StopWatch stop = new StopWatch();
		//1.新系统从基本信息表中初始化放款编号，放款金额
		logger.info("新系统开始贷后数据同步", "");
		
		logger.info("新系统开始同步新放款编号", "");
			initNewData();
		logger.info("新系统结束同步放款编号");
		/* 因为新系统的还款计划不是实时的所以注销掉
		//2.新系统同步放款余额
		logger.info("新系统开始同步放款余额");
			synchronousLoanDataByNew();
		logger.info("新系统结束同步放款余额");*/
		
		//3.新系统同步逾期数据
		logger.info("新系统开始同步逾期数据");
			//synchronousOverDueDataByNew();
		logger.info("新系统结束同步逾期数据");
		
		logger.info("新系统结束同步贷后，总耗时[{}]秒", stop.elapsedTime()/1000);
	}
	
	
	public void initData()  throws Exception{
		//老系统：RUN(还款中)，clo(还款完成)
		//新系统：o(已放款)，hN(还款完成)
		//已放款O，逾期YQ，代偿DC 状态会同步到贷后
		baseDao.deleteEntity("delete from basic_information_after_loan");
		String sql="insert into basic_information_after_loan(bill_code,loan_amount,source_type) select t.bill_code,t.bank_loan_amount,t.source_type from loan_base_info t"+
		" where t.islock ='N' and t.loan_time is not null";
		baseDao.excuteSql(sql);
	}
		
	
	
	//逾期数据
    public void synchronousOverDueData()  throws Exception{
    	int index = 0;
		int count1 =0;
		int count2 =0;
		int count3 =0;
		int count4 =0;
		int count5 =0;
		
		index=0;
		String sql1=" select billCode,sum(case when status='1' then defaultamount end),sum(case when status='2' then defaultamount end),sum(case when status='3' then disposalamoun end),sum(case when status='4' then disposalamoun end),sum(penaltyinterest),defaultdatenum from o2o_overdue_report where isnew = 'Y' group by billCode ";
		
		while (index >= 0) {
			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index, sql1);
			PageResult<Object[]> pageResult = this.baseDao.findDataWithPageByService(sqlPageQuery);
			Integer count = pageResult.getDbRowCount();
			logger.info("总逾期数据",count);
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				Object[] obj = pageResult.getData().get(i);
				//logger.info("total:{},{}", ++total, obj.toString());
					if(obj[1]!=null){
						baseDao.excuteSql("update basic_information_after_loan set overdue_interest = ?, last_op_date =sysdate() where bill_code = ?",obj[1],obj[0]);
						//logger.info("利息逾期：[{}]",obj[0]);
						++count1;
					}
					if(obj[2]!=null){
						baseDao.excuteSql("update basic_information_after_loan set overdue_principal = ?, last_op_date =sysdate() where bill_code = ?",obj[2],obj[0]);
						//logger.info("本金逾期",obj[0]);
						++count2;
					}
					if(obj[3]!=null){
						baseDao.excuteSql("update basic_information_after_loan set compensatory_amount= ?, last_op_date =sysdate() where bill_code = ?",obj[3],obj[0]);
						//logger.info("代偿",obj[0]);
						++count3;
					}
					if(obj[4]!=null){
						baseDao.excuteSql("update basic_information_after_loan set compensatory_amount= ?, last_op_date =sysdate() where bill_code = ?",obj[4],obj[0]);
						//logger.info("代偿",obj[0]);
						++count3;
					}
					if(obj[5]!=null){
						baseDao.excuteSql("update basic_information_after_loan set penalty_interest= ?, last_op_date =sysdate() where bill_code = ?",obj[5],obj[0]);
						//logger.info("罚息",obj[0]);
						++count4;
					}
					if(obj[6]!=null){
						baseDao.excuteSql("update basic_information_after_loan set overdue_principal_days= ?, last_op_date =sysdate() where bill_code = ?",obj[6],obj[0]);
						//logger.info("逾期天数",obj[0]);
						++count5;
					}
					
			}
			if (pageResult.getPageTotal() > index) {
				index++;
			} else {
				index = -1;
			}
		}
		logger.info("利息逾期条数:{}",count1);
		logger.info("本金逾期条数:{}",count2);
		logger.info("代偿数:{}",count3);
		logger.info("罚息:{}",count4);
		logger.info("逾期天数:{}",count5);
	}	
	
    
    
    /**
     * @Title: synchRepayStatus
     * @Description:增量同步还款状态，结清日(处理逻辑：首先所有置为正常还款，到期日已到的改为已结清，再看逾期是什么状态就置什么状态)
     * @author lwx
     * @return void
     * @throws
     * @date 2016年9月18日 下午1:24:37
     */
    public void synchRepayStatus(){
    	/*
    	 * 还款状态方案修改 2017.01.05
    	 * 关联单号：datasyn-10128 贷后报表 
    	 * 实现逻辑：首先同步还款状态，若有逾期，再同步逾期状态
    	 * 
    	 * 状态：未逾期：已结清（还款完成），正常还款（还款中）
    	 *      逾期：利息逾期，本金逾期，代偿，利息结清，本金结清
		 *
		 *
		 *新系统：还款完成：HN  已放款/审批通过：O（已放款的当做还款中）
    	 */
    	//老系统全量同步
    	StopWatch sp=new StopWatch();
    	//同步前，将所有的单置为未同步
    	baseDao.excuteSql("update basic_information_after_loan set repay_flag =0 ");
    	int index=0;
    	String sql="select t.bill_code from basic_information_after_loan t where t.repay_flag =0 and  t.source_type=0  ";
    	//台账数据：还款状态
    	String sqlOracle1="select  t.billstatus  from eadb.o2o_refund t  where t.loadapplycode = ? ";
    	//逾期数据：逾期状态
    	String sqlMysql2= "select case when t.status ='1' then 'OVER_1' when t.status ='2' then 'OVER_2' when t.status ='3' then 'OVER_3' when t.status ='4' then 'OVER_4'  end from o2o_overdue_report t  where t.billCode = ? order by t.disposaldate desc ";
    	logger.info("开始同步老系统数据的台账状态");
    	int count=0;
    	while (index >= 0) {
			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index, sql);
			PageResult<Object[]> pageResult = this.baseDao.findDataWithPageByMySql(sqlPageQuery);
			if(pageResult.getReturnDataSize() == 0){
				index=-1;//跳出循环
				break;
			}
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				Object[] obj = pageResult.getData().get(i);
				logger.info("开始同步老系统数据：[{}]",obj[0]);
				List<Object[]> list1=baseDao.findObjectsListByOracle(sqlOracle1,obj[0]);
				List<Object[]> list2=baseDao.findParamsForService(sqlMysql2,obj[0]);
				
				try {
					if(list1!=null&&list1.size()>0){
						Object[] o =list1.get(0);
						String normalStatus = o[0]==null?null:(String)o[0];//老系统中单的台账状态
						
						String overStatus="";
						if(list2!=null&&list2.size()>0){
							Object[] os =list2.get(0);
							overStatus = os[0]==null?null:(String)os[0];//老系统中单的逾期状态
						}
					
						if(StringUtil.isEmpty(overStatus)){
							baseDao.excuteSql("update basic_information_after_loan set repay_flag =1,repay_status = ? where bill_code =? ", normalStatus,obj[0]);
						}else{
							baseDao.excuteSql("update basic_information_after_loan set repay_flag =1,repay_status = ? where bill_code =? ", overStatus,obj[0]);
						}
					
					}
					baseDao.excuteSql("update basic_information_after_loan set repay_flag =1 where bill_code =? ", obj[0]);
				} catch (Exception e) {
					e.printStackTrace();
					logger.info("(旧)同步单：[{}]异常,异常为：[{}]",obj[0],e.getMessage());
				}
				count++;
				logger.info("结束同步老系统第[{}]条数据,单号为：[{}],耗时：[{}]",count,obj[0],sp.elapsedTime()/1000);
			}
		}
    	logger.info("结束同步老系统数据的台账状态,共[{}]条数据，共耗时：[{}]",count,sp.elapsedTime()/1000);
		
    	
    	
    	logger.info("开始同步新系统数据的台账状态");
    	index=0;
    	//新系统全量同步
    	String sql1="select t.bill_code from basic_information_after_loan t where   t.repay_flag =0 and t.source_type=1 ";
    	//台账数据
    	String sqlOracle4="select t.end_bank_date,case when t.wf_type  ='HN' then 'CLO'  when t.wf_type  ='O'   then 'RUN'  else  t.wf_type end  from o2o_loanapply  t  where "
    			+ "(t.data_source IS NULL OR t.data_source <> '2') and t.bill_code = ? ";
    	int count1=0;
    	while (index >= 0) {
			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index, sql1);
			PageResult<Object[]> pageResult = this.baseDao.findDataWithPageByMySql(sqlPageQuery);
			if(pageResult.getReturnDataSize() == 0)  return;
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				Object[] obj = pageResult.getData().get(i);
				logger.info("开始同步新系统数据：[{}]",obj[0]);
				List<Object[]> list3=serviceDao.findParams(sqlOracle4,obj[0]);
				List<Object[]> list4=baseDao.findParamsForService(sqlMysql2,obj[0]);
				//老系统已放款的单共两种状态：正常还款，还款完成	
				if(list3!=null&&list3.size()>0){
					Object[] o =list3.get(0);
					Date date = o[0]==null?null:(Date)o[0];//到期日
					String normalStatus = o[1]==null?null:(String)o[1];//还款状态
					
					String overStatus="";
					if(list4!=null&&list4.size()>0){
						Object[] os =list4.get(0);
						overStatus = os[0]==null?null:(String)os[0];//新系统中单的逾期状态
					}
					
					try {
						if(StringUtil.isEmpty(overStatus)){
								baseDao.excuteSql("update basic_information_after_loan set repay_flag =1,repay_status = ?,repay_date=? where bill_code =? ", normalStatus,date,obj[0]);
						    }else{
						    	baseDao.excuteSql("update basic_information_after_loan set repay_flag =1,repay_status = ?,repay_date=? where bill_code =? ", overStatus,date,obj[0]);
						    }
					} catch (Exception e) {
						e.printStackTrace();
						logger.info("(新)同步单：[{}]异常,异常为：[{}]",obj[0],e.getMessage());
					}
					
				}
				baseDao.excuteSql("update basic_information_after_loan set repay_flag =1 where bill_code =? ", obj[0]);
				count1++;
				logger.info("结束同步新系统第[{}]条数据,单号为：[{}],耗时：[{}]",count1,obj[0],sp.elapsedTime()/1000);
			}
		}
    	logger.info("结束同步新系统数据的台账状态,共[{}]条数据，共耗时：[{}]",count1,sp.elapsedTime()/1000);
    	
    	
    	
    	
    	
    	
    	
    	
    	
    	
    	
    	
    	/*//老系统全量同步
    	StopWatch sp=new StopWatch();
    	//同步前，将所有的单置为未同步
    	baseDao.excuteSql("update basic_information_after_loan set repay_flag =0 ");
    	int index=0;
    	String sql="select t.bill_code from basic_information_after_loan t where t.repay_flag =0 and  t.source_type=0  ";
    	//台账数据：还款状态
    	String sqlOracle1="select  t.billstatus  from eadb.o2o_refund t  where t.loadapplycode = ? ";
    	//到期日
    	String sqlMysql1=" select  DATE_ADD(loan_time,INTERVAL loan_term  month)  from loan_base_info where  bill_code =  ? ";
    	//逾期数据：逾期状态
    	String sqlMysql2= "select case when t.status ='1' then 'OVER_1' when t.status ='2' then 'OVER_2' when t.status ='3' then 'OVER_3' when t.status ='4' then 'OVER_4' when t.status ='5' then 'OVER_5'  end from basic_information_overdue t  where t.billCode = ? order by t.disposal_date desc ";
    	//还款计划
    	String sqlMysql3="select count(0) from payments_plan where bill_code = ? ";
    	logger.info("开始同步老系统数据的台账状态");
    	int count=0;
    	while (index >= 0) {
			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index, sql);
			PageResult<Object[]> pageResult = this.baseDao.findDataWithPageByMySql(sqlPageQuery);
			if(pageResult.getReturnDataSize() == 0){
				index=-1;//跳出循环
				break;
			}
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				Object[] obj = pageResult.getData().get(i);
				logger.info("开始同步老系统数据：[{}]",obj[0]);
				List<Object[]> list1=baseDao.findObjectsListByOracle(sqlOracle1,obj[0]);
				List<Object[]> list2=baseDao.findParams(sqlMysql2,obj[0]);
				List<Object[]> list3=baseDao.findParams(sqlMysql1,obj[0]);
				
				try {
					if(list1!=null&&list1.size()>0){
						Object[] o =list1.get(0);
						String normalStatus = o[0]==null?null:(String)o[0];//老系统中单的台账状态
						
						Date  date = null;
						if(list3!=null&&list3.size()>0){
							Object[] o3 =list3.get(0);
							date = o3[0]==null?null:(Date)o3[0];//到期日
						}
						
						String overStatus="";
						if(list2!=null&&list2.size()>0){
							Object[] os =list2.get(0);
							overStatus = os[0]==null?null:(String)os[0];//老系统中单的逾期状态
						}
					//老系统已放款的单共三种状态：银行驳回，正常还款，还款完成	
						//处理银行驳回
						if(!("RUN").equals(normalStatus)&&!("CLO").equals(normalStatus)){
							if(StringUtil.isEmpty(overStatus)){
								//到期日当天统计时，当天不统计在内(9.20统计时，到期日为9.20不算到期，9.19的算到期)
								//老系统：单据状态为“银行拒绝或者驳回”，同时到期日已到，则置为“还款完成”
								baseDao.excuteSql("update basic_information_after_loan set repay_flag =1,repay_status ='CLO',repay_date=? where bill_code =? and SUBDATE(sysdate(),INTERVAL 1 day) > ? ",date,obj[0],date);
								//老系统：单据状态为“银行拒绝或者驳回”，同时到期日未到，则置为“还款中”
								baseDao.excuteSql("update basic_information_after_loan set repay_flag =1,repay_status ='RUN',repay_date=? where bill_code =? and to_days( now( ) ) - to_days( modify_date) = 1 ",date,obj[0]);
							}else{
								baseDao.excuteSql("update basic_information_after_loan set repay_flag =1,repay_status = ? where bill_code =? ", overStatus,obj[0]);
							}
						}else{
							if(StringUtil.isEmpty(overStatus)){
								//1.全部数据置为台账表中的状态
								baseDao.excuteSql("update basic_information_after_loan set repay_status =?,repay_date=?,repay_flag =1 where bill_code =? " ,normalStatus,date,obj[0]);
								特殊处理：
										 * 逻辑： 到期日已到，同时也没有逾期，但是状态是正常还款
										 * 出现的原因：没有去点还款完成
										 * 解决：直接置为已结清
								 
								if(("RUN").equals(normalStatus)){
									//到期日当天统计时，当天不统计在内(9.20统计时，到期日为9.20不算到期，9.19的算到期)
									baseDao.excuteSql("update basic_information_after_loan set repay_flag =1,repay_status ='CLO',repay_date=? where bill_code =? and SUBDATE(sysdate(),INTERVAL 1 day) > ? ",date,obj[0],date);
								}
							}else{
								baseDao.excuteSql("update basic_information_after_loan set repay_flag =1,repay_status = ? where bill_code =? ", overStatus,obj[0]);
							}
						}
					}
				} catch (Exception e) {
					e.printStackTrace();
					logger.info("(旧)同步单：[{}]异常,异常为：[{}]",obj[0],e.getMessage());
				}
				count++;
				logger.info("结束同步老系统第[{}]条数据,单号为：[{}],耗时：[{}]",count,obj[0],sp.elapsedTime()/1000);
			}
		}
    	logger.info("结束同步老系统数据的台账状态,共[{}]条数据，共耗时：[{}]",count,sp.elapsedTime()/1000);
		
    	
    	
    	
    	
    	
    	
    	logger.info("开始同步新系统数据的台账状态");
    	index=0;
    	//新系统全量同步
    	String sql1="select t.bill_code from basic_information_after_loan t where   t.repay_flag =0 and t.source_type=1 ";
    	//台账数据
    	String sqlOracle4="select t.end_bank_date,case when t.wf_type  ='HN' then 'CLO'  when t.wf_type  ='O'   then 'RUN'  else '' end  from o2o_loanapply  t  where t.bill_code = ? ";
    	int count1=0;
    	while (index >= 0) {
			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index, sql1);
			PageResult<Object[]> pageResult = this.baseDao.findDataWithPageByMySql(sqlPageQuery);
			if(pageResult.getReturnDataSize() == 0)  return;
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				Object[] obj = pageResult.getData().get(i);
				logger.info("开始同步新系统数据：[{}]",obj[0]);
				List<Object[]> list3=serviceDao.findParams(sqlOracle4,obj[0]);
				List<Object[]> list4=baseDao.findParams(sqlMysql2,obj[0]);
				//老系统已放款的单共两种状态：正常还款，还款完成	
				if(list3!=null&&list3.size()>0){
					Object[] o =list3.get(0);
					Date date = o[0]==null?null:(Date)o[0];//到期日
					String normalStatus = o[1]==null?null:(String)o[1];//还款状态
					
					String overStatus="";
					if(list4!=null&&list4.size()>0){
						Object[] os =list4.get(0);
						overStatus = os[0]==null?null:(String)os[0];//新系统中单的逾期状态
					}
					
					try {
						if(StringUtil.isEmpty(overStatus)){
								baseDao.excuteSql("update basic_information_after_loan set repay_flag =1,repay_status = ?,repay_date=? where bill_code =? ", normalStatus,date,obj[0]);
								//到期日已到，因为没有逾期（没有去点还款完成）
								if(("RUN").equals(normalStatus)){
									//到期日当天统计时，当天不统计在内(9.20统计时，到期日为9.20不算到期，9.19的算到期)
									baseDao.excuteSql("update basic_information_after_loan set repay_flag =1,repay_status ='CLO',repay_date=? where bill_code =? and SUBDATE(sysdate(),INTERVAL 1 day) > ? ",date,obj[0],date);
								}
						    }else{
						    	baseDao.excuteSql("update basic_information_after_loan set repay_flag =1,repay_status = ? where bill_code =? ", overStatus,obj[0]);
						    }
					} catch (Exception e) {
						e.printStackTrace();
						logger.info("(新)同步单：[{}]异常,异常为：[{}]",obj[0],e.getMessage());
					}
					
				}
				count1++;
				logger.info("结束同步新系统第[{}]条数据,单号为：[{}],耗时：[{}]",count1,obj[0],sp.elapsedTime()/1000);
			}
		}
    	logger.info("结束同步新系统数据的台账状态,共[{}]条数据，共耗时：[{}]",count1,sp.elapsedTime()/1000);
    	*/
    	
    }
    
	
	/**
	 * @Title: synchronousLoanData
	 * @Description: 根据还款计修改放款余额
	 * @author 张庆奇
	 * @throws Exception
	 * @return void
	 * @throws
	 * @date 2016年12月7日 下午1:40:20
	 */
	public <T> void synchronousLoanData()  throws Exception{
		String sql="SELECT loan.bill_code, base.bank_loan_amount AS loanAmount, repay_status, base.loan_time AS last_op_date,  DATE_ADD(base.loan_time,INTERVAL base.loan_term month) repayDate FROM basic_information_after_loan loan LEFT JOIN loan_base_info base ON loan.bill_code = base.bill_code" ;
		int index = 0;
		int total = 0;
		 Calendar currCal=Calendar.getInstance(); 
		 int currentYear = currCal.get(Calendar.YEAR)-1;
		 Date lastYearDate=getYearLast(currentYear);
		while (index >= 0) {
			SqlPageQuery<BasicInformationAfterLoan> sqlPageQuery = new SqlPageQuery<>(BasicInformationAfterLoan.class, Constant.PROCESS_DATA_LIMIT,index, sql);
			PageResult<BasicInformationAfterLoan> pageResult = this.baseDao.findDataWithPageByMySql(sqlPageQuery);
			logger.debug("total:{},pagesize:{}", ++total, pageResult.getReturnDataSize());
			if (pageResult.getReturnDataSize() == 0)
				return;
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				BasicInformationAfterLoan obj = pageResult.getData().get(i);
				
				BigDecimal outstandingOfDeposits=refundBlanceAmount(obj.getBillCode(),obj.getLoanAmount(),obj.getRepayStatus(),0,obj.getRepayDate());
				try {
					baseDao.excuteSql("update basic_information_after_loan set outstanding_of_deposits = ?  where bill_code = ? ",outstandingOfDeposits,obj.getBillCode());
				} catch (Exception e) {
					logger.error("数据库异常", e);
				}
				
				if (lastYearDate.after(obj.getLastOpDate())) {
					BigDecimal last_year_banlance_amount = refundBlanceAmount(obj.getBillCode(), obj.getLoanAmount(),obj.getRepayStatus(), 1,obj.getRepayDate());
					try {
						baseDao.excuteSql("update basic_information_after_loan set last_year_banlance_amount = ?  where bill_code = ? ",
								last_year_banlance_amount, obj.getBillCode());
					} catch (Exception e) {
						logger.error("数据库异常", e);
					}
				}
			}

			if (pageResult.getPageTotal() > index + 1) {
				index++;
			} else {
				index = -1;
			}
		}
		
		
	}
	
	/**
	 * @Title: refundBlanceAmount
	 * @Description: 通过划款计划计算放款余额（若还款状态为clo则余额为0，若没有还款计划则余额为贷款金额，若状态不是clo且有还款计划则余额为还款计划中的金额）
	 * @author 张庆奇
	 * @param billcode
	 * @param loanAmount
	 * @param  repay_status 还款状态
	 * @return
	 * @return BigDecimal
	 * @throws
	 * @date 2016年12月7日 下午2:03:27
	 * 
	 * 注意：1.对于逾期的时候，贷款正常余额就是0了
	 * 这个是正常的，生成报表的时候，有一个全部余额，它包含了“正常余额”和“逾期数据”
	 * 2.当银行放款了，没有反馈数据给我们，会导致报表余额不准
	 * 3.当客户提前还款了，但是没有操作还款完成按钮，也会导致余额不准
	 */
	private BigDecimal refundBlanceAmount(String billcode,BigDecimal loanAmount,String repay_status,int type,Date repayDate){
		BigDecimal amount=new BigDecimal(0);
		//1.状态为已结清的置为0
		if(!StringUtils.isEmpty(repay_status)&&repay_status.equals("CLO")){
			return amount;
		}
		//2.到期日已到的置为0
		Date yestoday = new Date(new Date().getTime());
		if(yestoday.after(repayDate)){
			return amount;
		}
		String sql="";
		if(type==0){
			sql="select residual_principal from  payments_plan where bill_code=? and plan_date<= curdate() order by plan_date desc limit 1 " ;
		}else{
			sql="select residual_principal from  payments_plan where bill_code=? and  year(plan_date) <= year(date_sub(now(),interval 1 year)) order by plan_date desc limit 1 " ;

		}
		 
		//3.没有还款计划就取放款金额，有还款计划就取还款计划的余额
		PaymentsPlan entity=(PaymentsPlan) baseDao.findEntityForMysql(sql, PaymentsPlan.class, billcode);
		if(entity==null){
			amount=loanAmount;
		}else{
			amount=entity.getResidualPrincipal();
		}
		return amount;
		
		
	}

    
    
    
////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////  
    
    
	
	public void initNewData()  throws Exception{
		//老系统：RUN(还款中)，clo(还款完成)
		//新系统：o(已放款)，hN(还款完成)
		StringBuffer querySql=new StringBuffer();
		querySql.append("SELECT l.bill_code, l.bank_loan_amount,l.source_type FROM loan_base_info l WHERE l.islock = 'N' AND l.source_type = 1 and l.loan_time is not null");
		//querySql.append("AND ( l.refund_status = 'RUN' OR l.refund_status = 'CLO' OR l.refund_status = 'HN' OR l.refund_status = 'O' )");
		int pageIndex=0;
		int pageSize=100;
		while(pageIndex>=0){
			logger.info("loanAfterInfoTask新系统定时任务运行中，正在查询第[{}]页数据", pageIndex);
			SqlPageQuery<LoanBaseInfoEntity> sqlPageQuery = new SqlPageQuery<>(LoanBaseInfoEntity.class, pageSize,pageIndex, querySql.toString());
			PageResult<LoanBaseInfoEntity> results = this.baseDao.findDataWithPageByMySql(sqlPageQuery);
			
			List<LoanBaseInfoEntity> baseInfoList = results.getData();
			for(LoanBaseInfoEntity baseInfo : baseInfoList) {				
				boolean flag;
				String update="update basic_information_after_loan b set b.loan_amount=? where b.bill_code=?";
				flag=baseDao.excuteSql(update, baseInfo.getBankLoanAmount(),baseInfo.getBillCode());
				if(flag == false){
					String insertSql="insert into basic_information_after_loan(bill_code,loan_amount,source_type) values(?,?,?)";
					baseDao.excuteSql(insertSql,baseInfo.getBillCode(),baseInfo.getBankLoanAmount(),baseInfo.getSourceType());
				}
			}
			pageIndex++;
			if(results.getPageTotal()<=pageIndex) {
				pageIndex=-1;
			}
		}
	}
	
	/*public <T> void synchronousLoanData()  throws Exception{
		int index = 0;//第几页
		int total = 0;//第几条数据
		//mysql更新
		baseDao.excuteSql("update basic_information_after_loan set outstanding_of_deposits = loan_amount , last_op_date =sysdate() ");
		//旧系统台账数据
		//正常余额：
		//1.到期日未到，且没有还清的记为正常余额，逾期的就不算在其中。   
		//2.一个单如果逾期了，正常余额就记为0，如果未到期，未还清就记总额（因为现在只考虑“到期还本”情况）
		//3.到期日为统计日时的数据不统计到正常余额
		//4.add_months(o1.loaddate, o1.loanterm) < sysdate-2    (1   统计日不算在其中，1 sysdate带时分秒而到期日不带)
		String sql =" select o1.loadapplycode from eadb.o2o_refund o1 where  add_months(o1.loaddate, o1.loanterm) < sysdate-2 order by o1.loadapplycode asc";
		while (index >= 0) {
			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index, sql);
			//oracle查询
			PageResult<Object[]> pageResult = this.baseDao.findDataWithPage(sqlPageQuery);
			Integer count = pageResult.getDbRowCount();
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				Object[] obj = pageResult.getData().get(i);
				//logger.debug("total:{},{}", ++total, obj.toString());
					//mysql更新
				baseDao.excuteSql("update basic_information_after_loan set outstanding_of_deposits =0 where bill_code = ? and source_type=0",obj[0]);
			}

			if (pageResult.getPageTotal() > index) {
				index++;
			} else {
				index = -1;
			}
		}
		//新系统台账数据
		 index = 0;//第几页
		 total = 0;//第几条数据
		String sql1 = " select t.bill_code from o2o_bank_loan_data t where  ADDDATE(t.loan_date,INTERVAL t.loan_term  MONTH) < now()  ";
		while (index >= 0) {
			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index, sql1);
			//oracle查询
			PageResult<Object[]> pageResult = this.serviceDao.findDataWithPageForService(sqlPageQuery);
			Integer count = pageResult.getDbRowCount();
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				Object[] obj = pageResult.getData().get(i);
				//logger.debug("total:{},{}", ++total, obj.toString());
					//mysql更新
				baseDao.excuteSql("update basic_information_after_loan set outstanding_of_deposits =0 where bill_code = ? and source_type=1 ",obj[0]);
			}

			if (pageResult.getPageTotal() > index) {
				index++;
			} else {
				index = -1;
			}
		}
		
		
		
	}	
	
	
	
	public <T> void synchronousLoanDataByNew()  throws Exception{
		int index = 0;//第几页
		int total = 0;//第几条数据
		//mysql更新
		baseDao.excuteSql("update basic_information_after_loan set outstanding_of_deposits = loan_amount , last_op_date =sysdate()   where source_type=1 ");
		//正常余额：
		//1.到期日未到，且没有还清的记为正常余额，逾期的就不算在其中。   
		//2.一个单如果逾期了，正常余额就记为0，如果未到期，未还清就记总额（因为现在只考虑“到期还本”情况）
		//3.到期日为统计日时的数据不统计到正常余额
		//4.add_months(o1.loaddate, o1.loanterm) < sysdate-2    (1   统计日不算在其中，1 sysdate带时分秒而到期日不带)
		
		//新系统台账数据
		 index = 0;//第几页
		 total = 0;//第几条数据
		String sql1 = " select t.bill_code from o2o_bank_loan_data t where  ADDDATE(t.loan_date,INTERVAL t.loan_term  MONTH) < now()  ";
		while (index >= 0) {
			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index, sql1);
			//oracle查询
			PageResult<Object[]> pageResult = this.serviceDao.findDataWithPageForService(sqlPageQuery);
			Integer count = pageResult.getDbRowCount();
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				Object[] obj = pageResult.getData().get(i);
				//logger.debug("total:{},{}", ++total, obj.toString());
					//mysql更新
				baseDao.excuteSql("update basic_information_after_loan set outstanding_of_deposits =0 where bill_code = ? and source_type=1 ",obj[0]);
			}

			if (pageResult.getPageTotal() > index) {
				index++;
			} else {
				index = -1;
			}
		}
	}*/
	
	
	
	
  //新系统逾期数据(只同步新系统)
    @Deprecated
    public void synchronousOverDueDataByNew()  throws Exception{
    	int index = 0;
		int count1 =0;
		int count2 =0;
		int count3 =0;
		int count4 =0;
		int count5 =0;
		
		//新系统逾期数据
		String sqlNew ="select t.billcode,t.defaultinterest,t.defaultcapital,"+ 
				"case when t.STATUS = '3' then t.DISPOSALAMOUN end as dc,t.penaltyinterest , "+
               " t.defaultamount,t.status,t.disposalamoun,t.disposaldate,t.defaultdatenum from o2o_overdue_report t where  t.isnew='Y' ";
		while (index >= 0) {
			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index, sqlNew);
			PageResult<Object[]> pageResult = serviceDao.findDataWithPageForService(sqlPageQuery);
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				Object[] obj = pageResult.getData().get(i);
				baseDao.excuteSql("insert into basic_information_overdue(id,billCode,overdue_interest,overdue_principal,compensatory_amount,penalty_interest,op_time,is_new,default_amout,status,disposal_amount,disposal_date,overdue_principal_days)"+
						" values(?,?,?,?,?,?,now(),'Y',?,?,?,?,?)", DefaultIdBuilder.build(),obj[0],obj[1],obj[2],obj[3],obj[4],obj[5],obj[6],obj[7],obj[8],obj[9]);
			}
			if (pageResult.getPageTotal() > index) {
				index++;
			} else {
				index = -1;
			}
		}
		
		
		
		index=0;
		String sql1=" select billCode,sum(overdue_interest),sum(overdue_principal),sum(compensatory_amount),sum(penalty_interest),overdue_principal_days from basic_information_overdue where is_new = 'Y' group by billCode ";
		
		while (index >= 0) {
			SqlPageQuery<Object[]> sqlPageQuery = new SqlPageQuery<>(Object[].class, Constant.PROCESS_DATA_LIMIT, index, sql1);
			PageResult<Object[]> pageResult = this.baseDao.findDataWithPageByMySql(sqlPageQuery);
			Integer count = pageResult.getDbRowCount();
			logger.info("总逾期数据",count);
			for (int i = 0; i < pageResult.getReturnDataSize(); i++) {
				Object[] obj = pageResult.getData().get(i);
				//logger.info("total:{},{}", ++total, obj.toString());
					if(obj[1]!=null){
						baseDao.excuteSql("update basic_information_after_loan set overdue_interest = ?, last_op_date =sysdate() where bill_code = ?",obj[1],obj[0]);
						//logger.info("利息逾期：[{}]",obj[0]);
						++count1;
					}
					if(obj[2]!=null){
						baseDao.excuteSql("update basic_information_after_loan set overdue_principal = ?, last_op_date =sysdate() where bill_code = ?",obj[2],obj[0]);
						//logger.info("本金逾期",obj[0]);
						++count2;
					}
					if(obj[3]!=null){
						baseDao.excuteSql("update basic_information_after_loan set compensatory_amount= ?, last_op_date =sysdate() where bill_code = ?",obj[3],obj[0]);
						//logger.info("代偿",obj[0]);
						++count3;
					}
					if(obj[4]!=null){
						baseDao.excuteSql("update basic_information_after_loan set penalty_interest= ?, last_op_date =sysdate() where bill_code = ?",obj[4],obj[0]);
						//logger.info("罚息",obj[0]);
						++count4;
					}
					if(obj[5]!=null){
						baseDao.excuteSql("update basic_information_after_loan set overdue_principal_days= ?, last_op_date =sysdate() where bill_code = ?",obj[5],obj[0]);
						//logger.info("逾期天数",obj[0]);
						++count5;
					}
					
			}
			if (pageResult.getPageTotal() > index) {
				index++;
			} else {
				index = -1;
			}
		}
		logger.info("利息逾期条数:{}",count1);
		logger.info("本金逾期条数:{}",count2);
		logger.info("代偿数:{}",count3);
		logger.info("罚息:{}",count4);
		logger.info("逾期天数:{}",count5);
	}
    
    
    
  
    /**
     * 获取某年最后一天日期
     * @param year 年份
     * @return Date
     */
    public static Date getYearLast(int year){
        Calendar calendar = Calendar.getInstance();
        calendar.clear();
        calendar.set(Calendar.YEAR, year);
        calendar.roll(Calendar.DAY_OF_YEAR, -1);
        Date currYearLast = calendar.getTime();
         
        return currYearLast;
    }
    /**
     * 同步业务系统逾期数据
     */
   public void loadServiceOverdue(){
	   //删除逾期信息
	   String delSql="DELETE FROM basic_information_overdue";
	   baseDao.excuteSql(delSql);
	   logger.info("删除数据中心逾期"); 
	   String serviceSql="SELECT t.billcode,t.loanername,t.defaultcapital,t.defaultinterest,"
	   		+ "t.penaltyinterest,t.defaultamount,t.disposalamoun,t.defaultdatenum,t.timeframe"
	   		+ ",t.disposaldate,t.isnew,t.lastupdateuser,t.lastupdatetime,t.status from o2o_overdue_report t";
	   List<O2oOverdueReport> list=serviceDao.findEntitys(serviceSql, O2oOverdueReport.class);
	  int count=0;
	   if(list!=null && !list.isEmpty()){
		  for (O2oOverdueReport overdueReport : list) {
			   BasicInformationOverdue overdue=new BasicInformationOverdue(overdueReport.getBillcode(),overdueReport.getLoanername(), 
					   overdueReport.getDefaultcapital(), overdueReport.getDefaultinterest(),overdueReport.getDisposalamoun(),overdueReport.getPenaltyinterest(), 
					   overdueReport.getDefaultamount(),overdueReport.getDisposalamoun(),overdueReport.getDefaultdatenum(),
					   overdueReport.getTimeframe(),overdueReport.getDisposaldate(),overdueReport.getIsnew(),
					   overdueReport.getLastupdateuser(), new Date(),overdueReport.getStatus());
			   baseDao.saveEntity(overdue);
			   count++;
		}
	   }
	   logger.info("插入逾期条数:[{}]", count);
   }
    
}
